﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class C_ViolationDAL : BaseDAL<C_Violation>
    {
        public static readonly SqlConnectionStringBuilder ManageBuilder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
        public static readonly string ManageDatabase = ManageBuilder.InitialCatalog;
        #region
        //查询违章列表  尤浩 2015-11-27
        public object GetViolationList(int page, int rows, string start, string end, string center, string station, string actualCode, string ambulanceNumber, string place
                                       , string driverWorkID, string driverName, E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            List<ViolationExt> list = new List<ViolationExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct identity(int,1,1) as 行号,编码=vio.ID,司机工号=vio.DriverWorkID,司机姓名=vio.DriverName,车辆编码=vio.AmbulanceCode
                        ,备注=vio.Remark,车牌号码=vio.ActualCode,填写人=vio.FillPerson,填写时间=vio.FillTime,分中心编码=vio.CenterID,
                        违章时间=convert(varchar(10),vio.ViolationTime,120),分中心=tc.名称,分站编码=vio.StationID,分站=ts.名称,车辆编号=vio.AmbulanceNumber,
                        违章内容=vio.ViolationContent,违章地点=vio.ViolationPlace,处理时间=convert(varchar(10),vio.DisposeTime, 120),处理结果=vio.DisposeResult
                        into #tempa                   
                        from C_Violation vio 
                       ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on vio.StationID=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on vio.CenterID=tc.编码 ");
            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("vio.ViolationTime", Convert.ToDateTime(start), sb);
            WhereClauseUtility.AddDateTimeLessThan("vio.ViolationTime", Convert.ToDateTime(end), sb);
            WhereClauseUtility.AddStringLike("vio.ViolationPlace", place, sb);
            WhereClauseUtility.AddStringEqual("vio.DriverWorkID", driverWorkID, sb);
            WhereClauseUtility.AddStringEqual("vio.DriverName", driverName, sb);
            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddStringEqual("vio.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("vio.StationID", station, sb);
                    WhereClauseUtility.AddStringEqual("vio.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("vio.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("vio.CenterID", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("vio.StationID", station, sb);
                    WhereClauseUtility.AddStringEqual("vio.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("vio.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    sb.Append("and 1=2");
                    WhereClauseUtility.AddStringEqual("vio.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("vio.StationID", selfStationID, sb);
                    WhereClauseUtility.AddStringEqual("vio.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("vio.AmbulanceNumber", ambulanceNumber, sb);
                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    sb.Append("and 1=2");
                    WhereClauseUtility.AddStringEqual("vio.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("vio.StationID", station, sb);
                    WhereClauseUtility.AddStringEqual("vio.DriverWorkID", selfWorkCode, sb);
                    WhereClauseUtility.AddStringEqual("vio.ActualCode", actualCode, sb);
                    WhereClauseUtility.AddStringEqual("vio.AmbulanceNumber", ambulanceNumber, sb);
                }
                sb.Append(" order by 违章时间 desc");
                sb.Append(@" select * into #temp from #tempa where 1=1 ");
                sb.Append(@" select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
                sb.Append(@"  SELECT count(*) FROM #temp t   drop table #tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        ViolationExt info = new ViolationExt();
                        info.CenterID = dr["分中心编码"].ToString();
                        info.StationID = dr["分站编码"].ToString();
                        info.ID = Convert.ToInt32(dr["编码"]);
                        info.ViolationTime = dr["违章时间"].ToString();
                        info.ViolationPlace = dr["违章地点"].ToString();
                        info.ViolationContent = dr["违章内容"].ToString();
                        info.DriverName = dr["司机姓名"].ToString();
                        info.DriverWorkID = dr["司机工号"].ToString();
                        info.Remark = dr["备注"].ToString();
                        info.FillPerson = dr["填写人"].ToString();
                        info.FillTime = dr["填写时间"].ToString();
                        info.DisposeTime = dr["处理时间"].ToString();
                        info.DisposeResult = dr["处理结果"].ToString();
                        info.Center = dr["分中心"].ToString();
                        info.Station = dr["分站"].ToString();
                        info.AmbulanceCode = dr["车辆编码"].ToString();
                        info.AmbulanceNumber = dr["车辆编号"].ToString();
                        info.ActualCode = dr["车牌号码"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
        }

        public object GetCenterList()
        {
            List<P_User> list = new List<P_User>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码,名称  from TCenter where 是否有效=1 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_User info = new P_User();                  
                    info.ID = Convert.ToInt32(dr["编码"]);                   
                    info.Name = dr["名称"].ToString();
                    list.Add(info);
                }
            }         
            return list;

        }
        public object GetStationByCenterList(int centerID)
        {
            List<P_User> list = new List<P_User>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码,名称  from TStation where 是否有效=1 and 中心编码='"+centerID+"'");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    P_User info = new P_User();
                    info.ID = Convert.ToInt32(dr["编码"]);
                    info.Name = dr["名称"].ToString();
                    list.Add(info);
                }
            }
            return list;

        }
        #endregion
    }
}
